﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Data;
using Microsoft.SqlServer.Management.Common;
using SQLToolbox.Types;



namespace SQLToolbox.DAL
{

    /// <summary>
    /// Represents the locator of server instances of MSSQL 2005 on the intranet
    /// </summary>
    public class SMOServerLocator : SQLToolbox.Types.IServerLocator
    {
        DateTime lastServerEnumerationTime = DateTime.MinValue;
        List<string> serverNames = new List<string>();

        /// <summary>
        /// Enumerates the servers.
        /// </summary>
        private void EnumerateServers()
        {
            DataTable dt = SmoApplication.EnumAvailableSqlServers();
            serverNames.Clear();
            foreach (DataRow row in dt.Rows)
            {
                serverNames.Add(row["Name"].ToString());
            }
        }
        /// <summary>
        /// Gets the servers list.
        /// </summary>
        /// <value>The servers list.</value>
        public IEnumerable<string> ServersList
        {
            get
            {
                if (lastServerEnumerationTime.AddMinutes(1) <= DateTime.Now)
                {
                    EnumerateServers();
                }
                foreach (string s in serverNames)
                {
                    yield return s;
                }
            }

        }
    }

    /// <summary>
    /// Represent the provider of access to the database schema 
    /// </summary>
    public class SMOServerSchemaProvider : SQLToolbox.Types.IServerSchemaProvider
    {
        Server server;

        public SMOServerSchemaProvider()
        { ;}

        /// <summary>
        /// Connects to SQLServer 2005 using SQL Authentication
        /// </summary>
        /// <param name="serverName">Name of the server to connect to</param>
        /// <param name="username">login to use</param>
        /// <param name="password">password</param>
        public void SetConnection(DBConnectionProperties dcp)
        {
            server = SMOHelper.ConnectToServer(dcp, false);
        }

        public string ServerVersion
        {
            get
            {
                try
                {
                    return server.Information.VersionString;
                }
                catch (ConnectionFailureException ex)
                {
                    throw new SQLToolbox.Types.SQLTooboxDALException("Couldnot retrieve server's version", ex);
                }
            }
        }

        /// <summary>
        /// Enumerates the databases on the server.
        /// </summary>
        /// <value>The databases.</value>
        public List<string> Databases
        {
            get
            {
                if (server.Databases == null)
                    throw new SQLToolbox.Types.SQLTooboxDALException("Could not retrieve databases from the server");
                try
                {
                    int i = server.Databases.Count;
                }
                catch (ConnectionFailureException ex)
                {
                    throw new SQLToolbox.Types.SQLTooboxDALException("Could not retrieve databases from the server", ex);
                }

                return server.Databases.Cast<Database>().Select((db, s) => { return db.Name; }).ToList();
            }

        }



        public SQLToolbox.Types.Schema GetSchema(string databaseName)
        {
            try
            {
                if (server.Databases[databaseName] == null)
                    throw new SQLToolbox.Types.SQLTooboxDALException("No such database");
            }
            catch (ConnectionFailureException ex)
            {
                throw new SQLToolbox.Types.SQLTooboxDALException("Could not connect to the server", ex);
            }
            Database DB = server.Databases[databaseName];
            SQLToolbox.Types.Schema schema = new SQLToolbox.Types.Schema();
            EnumerateSMOOBjectsToSchema(DB.Tables, schema.Tables, null);
            EnumerateSMOOBjectsToSchema(DB.Views, schema.Views, null);
            EnumerateSMOOBjectsToSchema(DB.StoredProcedures, schema.Procedures, null);
            EnumerateSMOOBjectsToSchema(DB.UserDefinedFunctions, schema.UserDefinedFunctions, null);
            EnumerateSMOOBjectsToSchema(DB.UserDefinedTypes, schema.UserDefinedTypes, null);
            EnumerateSMOOBjectsToSchema(DB.UserDefinedDataTypes, schema.UserDefinedDataTypes, null);
            return schema;
        }



        public SQLToolbox.Types.Schema GetSchema(string databaseName, IDictionary<SchemaObjectType, IEnumerable<string>> requiredProperties)
        {
            try
            {
                if (server.Databases[databaseName] == null)
                    throw new SQLToolbox.Types.SQLTooboxDALException("No such database");
            }
            catch (ConnectionFailureException ex)
            {
                throw new SQLToolbox.Types.SQLTooboxDALException("Could not connect to the server", ex);
            }
            SMOHelper.SetDefaultInitFields(server, requiredProperties);
            Database DB = server.Databases[databaseName];
            SQLToolbox.Types.Schema schema = new SQLToolbox.Types.Schema();
            EnumerateSMOOBjectsToSchema(DB.Tables, schema.Tables, requiredProperties);
            EnumerateSMOOBjectsToSchema(DB.Views, schema.Views, requiredProperties);
            EnumerateSMOOBjectsToSchema(DB.StoredProcedures, schema.Procedures, requiredProperties);
            EnumerateSMOOBjectsToSchema(DB.UserDefinedFunctions, schema.UserDefinedFunctions, requiredProperties);
            EnumerateSMOOBjectsToSchema(DB.UserDefinedTypes, schema.UserDefinedTypes, requiredProperties);
            EnumerateSMOOBjectsToSchema(DB.UserDefinedDataTypes, schema.UserDefinedDataTypes, requiredProperties);
            return schema;
        }

        public event EventHandler<DatabaseObjectProcessingEventArgs> DatabaseObjectProcessing;

        private void FireDatabaseObjectProcessing(string objectName, SchemaObjectType type)
        {
            if (DatabaseObjectProcessing != null)
            {
                DatabaseObjectProcessing(this, new DatabaseObjectProcessingEventArgs { ObjectName = objectName, ObjectType = type });
            }
        }

        private void EnumerateSMOOBjectsToSchema(SchemaCollectionBase collection, List<SQLToolbox.Types.SchemaObject> listOfSchemaObjects, IDictionary<SchemaObjectType, IEnumerable<string>> requiredProperties)
        {
            bool checkForSystemType = true;
            try
            {
                var enm = collection.GetEnumerator();
                enm.MoveNext();
                object o = ((ScriptSchemaObjectBase)enm.Current).Properties["IsSystemObject"].Value;
            }
            catch
            {
                checkForSystemType = false;
            }

            foreach (ScriptSchemaObjectBase t in collection.Cast<ScriptSchemaObjectBase>().Where(tt => checkForSystemType ? !(bool)tt.Properties["IsSystemObject"].Value : true))
                listOfSchemaObjects.Add(ReadObject(t, requiredProperties));
        }


        private SQLToolbox.Types.SchemaObject ReadObject(ScriptSchemaObjectBase obj, IDictionary<SchemaObjectType, IEnumerable<string>> requiredProperties)
        {
            var type = GetObjectType(obj);
            FireDatabaseObjectProcessing(obj.Name, type);
            var ret = new SQLToolbox.Types.SchemaObject
                {
                    Name = obj.Name,
                    Type = type,
                    Schema = obj.Schema,
                    Script = new Script(((IScriptable)obj).Script(SMOHelper.CreateScriptingOptions()))
                };
            if ((requiredProperties != null) && (requiredProperties.ContainsKey(ret.Type)))
                foreach (string name in requiredProperties[ret.Type])
                    ret.RequestedProperties[name] = obj.Properties[name].Value;
            if (obj is Table)
                foreach (Index ix in ((Table)obj).Indexes)
                    if (ix.IndexKeyType == IndexKeyType.DriPrimaryKey)
                        ret.Script.AddRange(ix.Script(SMOHelper.CreateScriptingOptions()).Cast<string>().ToArray());
            if (obj is TableViewBase)
                ReadAttachedObjects(obj as TableViewBase, ret.AttachedObjects, requiredProperties);
            return ret;
        }

        private SQLToolbox.Types.SchemaObject ReadObject(ScriptNameObjectBase obj, IDictionary<SchemaObjectType, IEnumerable<string>> requiredProperties)
        {
            var type = GetObjectType(obj);
            FireDatabaseObjectProcessing(obj.Name, type);
            var ret = new SQLToolbox.Types.SchemaObject
            {
                Name = obj.Name,
                Type = type,
                Script = new Script(((IScriptable)obj).Script(SMOHelper.CreateScriptingOptionsWithForeignKeys()))
            };
            if ((requiredProperties != null) && (requiredProperties.ContainsKey(ret.Type)))
                foreach (string name in requiredProperties[ret.Type])
                    ret.RequestedProperties[name] = obj.Properties[name].Value;
            return ret;
        }

        private void ReadAttachedObjects(TableViewBase tvb, SchemaObjectsCollection coll, IDictionary<SchemaObjectType, IEnumerable<string>> requiredProperties)
        {
            foreach (Index ix in tvb.Indexes)
                if (ix.IndexKeyType != IndexKeyType.DriPrimaryKey)
                    coll.Add(ReadObject(ix, requiredProperties));
            foreach (ScriptNameObjectBase obj in tvb.Triggers)
                coll.Add(ReadObject(obj, requiredProperties));
            foreach (Statistic stc in tvb.Statistics)
                if (!stc.IsFromIndexCreation)
                    coll.Add(ReadObject(stc, requiredProperties));
            if (tvb is Table)
                foreach (ScriptNameObjectBase obj in ((Table)tvb).ForeignKeys)
                    coll.Add(ReadObject(obj, requiredProperties));

        }
        public SQLToolbox.Types.SchemaObject GetObject(string databaseName, SQLToolbox.Types.SchemaObjectType type, string objectName)
        {
            try
            {
                if (server.Databases[databaseName] == null)
                    throw new SQLToolbox.Types.SQLTooboxDALException("No such database");
            }
            catch (ConnectionFailureException ex)
            {
                throw new SQLToolbox.Types.SQLTooboxDALException("Could not connect to the server", ex);
            }
            Database DB = server.Databases[databaseName];

            switch (type)
            {
                case SQLToolbox.Types.SchemaObjectType.Table:
                    return DB.Tables[objectName] != null ? ReadObject(DB.Tables[objectName], null) : null;
                case SQLToolbox.Types.SchemaObjectType.View:
                    return DB.Views[objectName] != null ? ReadObject(DB.Views[objectName], null) : null; ;
                case SQLToolbox.Types.SchemaObjectType.Procedure:
                    return DB.StoredProcedures[objectName] != null ? ReadObject(DB.StoredProcedures[objectName], null) : null; ;
                case SQLToolbox.Types.SchemaObjectType.UserDefinedType:
                    return DB.UserDefinedTypes[objectName] != null ? ReadObject(DB.UserDefinedTypes[objectName], null) : null; ;
                case SQLToolbox.Types.SchemaObjectType.UserDefinedDataType:
                    return DB.UserDefinedDataTypes[objectName] != null ? ReadObject(DB.UserDefinedDataTypes[objectName], null) : null; ;
                case SQLToolbox.Types.SchemaObjectType.UserDefinedFunction:
                    return DB.UserDefinedFunctions[objectName] != null ? ReadObject(DB.UserDefinedFunctions[objectName], null) : null; ;
                case SQLToolbox.Types.SchemaObjectType.Login:
                case SQLToolbox.Types.SchemaObjectType.NotApplicable:
                default:
                    break;
            }

            return null;
        }

        private static SQLToolbox.Types.SchemaObjectType GetObjectType(ScriptNameObjectBase obj)
        {
            switch (obj.GetType().FullName)
            {
                case "Microsoft.SqlServer.Management.Smo.Table":
                    return SQLToolbox.Types.SchemaObjectType.Table;
                case "Microsoft.SqlServer.Management.Smo.View":
                    return SQLToolbox.Types.SchemaObjectType.View;
                case "Microsoft.SqlServer.Management.Smo.StoredProcedure":
                    return SQLToolbox.Types.SchemaObjectType.Procedure;
                case "Microsoft.SqlServer.Management.Smo.UserDefinedFunction":
                    return SQLToolbox.Types.SchemaObjectType.UserDefinedFunction;
                case "Microsoft.SqlServer.Management.Smo.UserDefinedType":
                    return SQLToolbox.Types.SchemaObjectType.UserDefinedType;
                case "Microsoft.SqlServer.Management.Smo.UserDefinedDataType":
                    return SQLToolbox.Types.SchemaObjectType.UserDefinedDataType;
                case "Microsoft.SqlServer.Management.Smo.Index":
                    return SQLToolbox.Types.SchemaObjectType.Index;
                case "Microsoft.SqlServer.Management.Smo.ForeignKey":
                    return SQLToolbox.Types.SchemaObjectType.ForeignKey;
                case "Microsoft.SqlServer.Management.Smo.Statistic":
                    return SQLToolbox.Types.SchemaObjectType.Statistic;
                case "Microsoft.SqlServer.Management.Smo.Trigger":
                    return SQLToolbox.Types.SchemaObjectType.Trigger;
                default:
                    break;
            }
            return SQLToolbox.Types.SchemaObjectType.NotApplicable;
        }

    }



    /// <summary>
    /// Matches objects in databases
    /// </summary>
    public class SMOObjectsMatcher : ISchemaObjectsMatcher
    {

        #region ISchemaObjectsMatcher Members

        private Database source = null, target = null;
        public void InitSourceDatabase(DBConnectionProperties sourceConnection)
        {
            source = GetDatabase(SMOHelper.ConnectToServer(sourceConnection, true), sourceConnection.DatabaseName);
        }

        public void InitTargetDatabase(DBConnectionProperties targetConnection)
        {
            target = GetDatabase(SMOHelper.ConnectToServer(targetConnection, true), targetConnection.DatabaseName);
        }

        /// <summary>
        /// Creates the alter script.
        /// </summary>
        /// <param name="sourceConnection">The source connection.</param>
        /// <param name="targetConnection">The target connection.</param>
        /// <param name="obj">The obj.</param>
        public void CreateAlterScript(SQLToolbox.Types.SchemaObjectBase obj)
        {
            if (source == null || target == null)
                throw new SQLToolbox.Types.SQLTooboxDALException("Alter action requires source and target databases to be initialized");
            CatchExceptions(() => _CreateAlterScript(obj));
        }

        /// <summary>
        /// Creates the drop script.
        /// </summary>
        /// <param name="targetConnection">The target connection.</param>
        /// <param name="obj">The obj.</param>
        public void CreateDropScript(SQLToolbox.Types.SchemaObjectBase obj)
        {
            if (target == null)
                throw new SQLToolbox.Types.SQLTooboxDALException("Drop action requires target database to be initialized");
            CatchExceptions(() => _CreateDropScript(obj));
        }

        public Script FinishBatch()
        {
            Database t = target;
            source = null;
            target = null;
            return ExtractScript(t.Parent);
        }

        #endregion
        private void _CreateAlterScript(SQLToolbox.Types.SchemaObjectBase obj)
        {
            switch (obj.Type)
            {
                case SchemaObjectType.Table:
                    MatchTables(source.Tables[obj.Name], target.Tables[obj.Name]);
                    break;
                case SchemaObjectType.View:
                    target.Views[obj.Name].Drop();
                    target.Views.Add(source.Views[obj.Name]);
                    break;
                case SchemaObjectType.Procedure:
                    target.StoredProcedures[obj.Name].Drop();
                    CreateDBObject(target, source.StoredProcedures[obj.Name]);
                    break;
                case SchemaObjectType.UserDefinedType:
                    target.UserDefinedTypes[obj.Name].Drop();
                    CreateDBObject(target, source.StoredProcedures[obj.Name]);
                    break;
                case SchemaObjectType.UserDefinedDataType:
                    target.UserDefinedDataTypes[obj.Name].Drop();
                    CreateDBObject(target, source.StoredProcedures[obj.Name]);
                    break;
                case SchemaObjectType.UserDefinedFunction:
                    target.UserDefinedFunctions[obj.Name].Drop();
                    CreateDBObject(target, source.StoredProcedures[obj.Name]);
                    break;
                case SchemaObjectType.Login:
                case SchemaObjectType.Trigger:
                case SchemaObjectType.Index:
                case SchemaObjectType.ForeignKey:
                case SchemaObjectType.Statistic:
                case SchemaObjectType.NotApplicable:
                default:
                    return ;
            }
        }


        private void CreateDBObject(Database db, ScriptSchemaObjectBase obj)
        {
            var script = new Script(((IScriptable)obj).Script(SMOHelper.CreateScriptingOptions()));

            foreach (string row in script)
            {
                db.ExecuteNonQuery(row);
            }

        }

        private void MatchTables(Table sourceTable, Table targetTable)
        {


            MatchObjectsInTables<Column>(sourceTable.Columns, targetTable.Columns, (source) =>
            {
                return new Column
                {
                    Name = source.Name,
                    Parent = targetTable,
                    Collation = source.Collation,
                    Computed = source.Computed,
                    ComputedText = source.ComputedText,
                    DataType = source.DataType,
                    Default = source.Default,
                    DefaultSchema = source.DefaultSchema,
                    Identity = source.Identity,
                    IdentityIncrement = source.IdentityIncrement,
                    IdentitySeed = source.IdentitySeed,
                    IsPersisted = source.IsPersisted,
                    NotForReplication = source.NotForReplication,
                    Nullable = source.Nullable,
                    RowGuidCol = source.RowGuidCol,
                    Rule = source.Rule,
                    RuleSchema = source.RuleSchema,
                    UserData = source.UserData
                };

            });
            MatchIndexesInTables(sourceTable.Indexes, targetTable.Indexes);

            MatchDropAndCreateObjectsInTables<Trigger>(sourceTable.Triggers, targetTable.Triggers, (source) =>
                {
                    var t = new Trigger(targetTable, source.Name);

                    t.TextBody = source.TextBody;
                    t.TextHeader = source.TextHeader;
                    t.TextMode = source.TextMode;
                    if (!t.TextMode)
                    {
                        t.Delete = source.Delete;
                        t.DeleteOrder = source.DeleteOrder;
                        t.Insert = source.Insert;
                        t.InsertOrder = source.InsertOrder;
                        t.Update = source.Update;
                        t.UpdateOrder = source.UpdateOrder;
                        t.InsteadOf = source.InsteadOf;
                        t.IsEncrypted = source.IsEncrypted;
                        t.NotForReplication = source.NotForReplication;
                    }
                    t.ExecutionContext = source.ExecutionContext;
                    t.ExecutionContextPrincipal = source.ExecutionContextPrincipal;
                    t.ImplementationType = source.ImplementationType;
                    t.IsEnabled = source.IsEnabled;
                    t.MethodName = source.MethodName;
                    t.QuotedIdentifierStatus = source.QuotedIdentifierStatus;
                    return t;
                });

            MatchDropAndCreateObjectsInTables<ForeignKey>(sourceTable.ForeignKeys, targetTable.ForeignKeys, (source) =>
            {
                var t = new ForeignKey
                {
                    Name = source.Name,
                    Parent = targetTable,
                    DeleteAction = source.DeleteAction,
                    IsChecked = source.IsChecked,
                    IsEnabled = source.IsEnabled,
                    NotForReplication = source.NotForReplication,
                    ReferencedTableSchema = source.ReferencedTableSchema,
                    ReferencedTable = source.ReferencedTable,
                    ScriptReferencedTable = source.ScriptReferencedTable,
                    ScriptReferencedTableSchema = source.ScriptReferencedTableSchema,
                    UpdateAction = source.UpdateAction
                };
                foreach (ForeignKeyColumn fkc in source.Columns)
                {
                    t.Columns.Add(new ForeignKeyColumn
                    {
                        Name = fkc.Name,
                        Parent = t,
                        ReferencedColumn = fkc.ReferencedColumn,
                    });
                }
                return t;
            });

            MatchDropAndCreateObjectsInTables<Statistic>(sourceTable.Statistics, targetTable.Statistics, (source) =>
            {
                var t = new Statistic
                {
                    Name = source.Name,
                    Parent = targetTable,
                    FileGroup = source.FileGroup,
                    NoAutomaticRecomputation = source.NoAutomaticRecomputation,
                };



                foreach (StatisticColumn fkc in source.StatisticColumns)
                {
                    t.StatisticColumns.Add(new StatisticColumn
                    {
                        Name = fkc.Name,
                        Parent = t,
                    });
                }
                return t;
            });

            targetTable.Alter();



        }
        private Func<Index, Index> CloneIndex(SqlSmoObject parent)
        {
            return (source) =>
                {
                    var t = new Index
                        {
                            Name = source.Name,
                            Parent = parent,
                            CompactLargeObjects = source.CompactLargeObjects,
                            DisallowPageLocks = source.DisallowPageLocks,
                            DisallowRowLocks = source.DisallowRowLocks,
                            FileGroup = source.FileGroup,
                            FillFactor = source.FillFactor,
                            IgnoreDuplicateKeys = source.IgnoreDuplicateKeys,
                            IndexKeyType = source.IndexKeyType,
                            IsClustered = source.IsClustered,
                            IsFullTextKey = source.IsFullTextKey,
                            IsUnique = source.IsUnique,
                            MaximumDegreeOfParallelism = source.MaximumDegreeOfParallelism,
                            NoAutomaticRecomputation = source.NoAutomaticRecomputation,
                            OnlineIndexOperation = source.OnlineIndexOperation,
                            PadIndex = source.PadIndex,
                            ParentXmlIndex = source.ParentXmlIndex,
                            PartitionScheme = source.PartitionScheme,
                            SecondaryXmlIndexType = source.SecondaryXmlIndexType,
                            SortInTempdb = source.SortInTempdb,
                            //Events;ExtendedProperties;ID;IsDisabled;IsIndexOnComputed;IsIndexOnTable;
                            //IsPartitioned;IsSystemNamed;IsSystemObject;IsXmlIndex ;PartitionSchemeParameters ;
                            //SpaceUsed;State;Urn;UserData
                        };
                    foreach (IndexedColumn ic in source.IndexedColumns)
                    {
                        t.IndexedColumns.Add(new IndexedColumn
                        {
                            Name = ic.Name,
                            Parent = t,
                            Descending = ic.Descending,
                            IsIncluded = ic.IsIncluded,
                            //State ;UserData
                        });
                    }
                    return t;
                };
        }

        private void MatchObjectsInTables<T>(SmoCollectionBase source, SmoCollectionBase target, Func<T, T> copyFunc) where T : NamedSmoObject
        {
            var dictSource = new Dictionary<string, T>();
            var dictTarget = new Dictionary<string, T>();
            foreach (T obj in source)
            {
                dictSource.Add(obj.Name.ToLowerInvariant(), obj);
            }
            foreach (T obj in target)
            {
                dictTarget.Add(obj.Name.ToLowerInvariant(), obj);
            }
            foreach (string name in dictSource.Keys)
            {
                if (!dictTarget.ContainsKey(name))
                {
                    var retVal = copyFunc(dictSource[name]);
                    if (retVal is ICreatable)
                        ((ICreatable)retVal).Create();
                    //Type type = typeof(T);
                    //System.Reflection.MethodInfo mi = target.GetType().GetMethod("Add", new Type[] { type });
                    //if (mi != null)
                    //    mi.Invoke(target, new object[] { retVal });
                }
                else
                    MatchObjects(dictSource[name], dictTarget[name]);
            }
            foreach (string name in dictTarget.Keys)
                if (!dictSource.ContainsKey(name))
                    if (dictTarget[name] is IDroppable)
                        ((IDroppable)dictTarget[name]).Drop();
        }

        private void MatchDropAndCreateObjectsInTables<T>(SmoCollectionBase source, SmoCollectionBase target, Func<T, T> copyFunc) where T : NamedSmoObject
        {
            var dictSource = new Dictionary<string, T>();
            var dictTarget = new Dictionary<string, T>();
            foreach (T obj in source)
            {
                if (IsIgnorableObject(obj))
                    continue;
                dictSource.Add(obj.Name.ToLowerInvariant(), obj);
            }
            foreach (T obj in target)
            {
                if (IsIgnorableObject(obj))
                    continue;
                dictTarget.Add(obj.Name.ToLowerInvariant(), obj);
            }
            foreach (string name in dictSource.Keys)
            {
                if (!dictTarget.ContainsKey(name))
                {
                    var retVal = copyFunc(dictSource[name]);
                    if (retVal is ICreatable)
                        ((ICreatable)retVal).Create();
                    //Type type = typeof(T);
                    //System.Reflection.MethodInfo mi = target.GetType().GetMethod("Add", new Type[] { type });
                    //if (mi != null)
                    //    mi.Invoke(target, new object[] { retVal });
                }
                else
                    if (!AreEqual(dictSource[name], dictTarget[name]))
                    {
                        var retVal = copyFunc(dictSource[name]);
                        if (dictTarget[name] is IDroppable)
                            ((IDroppable)dictTarget[name]).Drop();
                        if (retVal is ICreatable)
                            ((ICreatable)retVal).Create();
                    }

            }
            foreach (string name in dictTarget.Keys)
                if (!dictSource.ContainsKey(name))
                    if (dictTarget[name] is IDroppable)
                        ((IDroppable)dictTarget[name]).Drop();
        }

        private bool IsIgnorableObject(NamedSmoObject obj)
        {
            //Ignore primary key indices
            //if (obj is Index && (obj as Index).IndexKeyType == IndexKeyType.DriPrimaryKey)
            //    return true;
            //Ignore statistics which are based on indices or are auto-created
            if (obj is Statistic && ((obj as Statistic).IsFromIndexCreation || (obj as Statistic).IsAutoCreated))
                return true;
            return false;
        }

        private void MatchIndexesInTables(IndexCollection source, IndexCollection target)
        {
            var dictSource = new Dictionary<string, Index>();
            var dictTarget = new Dictionary<string, Index>();
            foreach (Index obj in source)
            {
                //Ignore primary key indices
                //if (obj is Index && (obj as Index).IndexKeyType == IndexKeyType.DriPrimaryKey)
                //    continue;
                dictSource.Add(obj.Name.ToLowerInvariant(), obj);
            }
            foreach (Index obj in target)
            {
                //Ignore primary key indices
                //if (obj is Index && (obj as Index).IndexKeyType == IndexKeyType.DriPrimaryKey)
                //    continue;
                dictTarget.Add(obj.Name.ToLowerInvariant(), obj);
            }
            foreach (string name in dictSource.Keys)
            {
                if (!dictTarget.ContainsKey(name))
                {
                    var retVal = CloneIndex(target.Parent)(dictSource[name]);
                    retVal.Create();
                }
                else
                    if (!AreEqual(dictSource[name], dictTarget[name]))
                    {
                        var retVal = CloneIndex(target.Parent)(dictSource[name]);
                        dictTarget[name].Drop();
                        retVal.Create();
                    }

            }
            foreach (string name in dictTarget.Keys)
                if (!dictSource.ContainsKey(name))
                //if (dictTarget[name].IndexKeyType != IndexKeyType.DriPrimaryKey)
                {
                    dictTarget[name].Refresh();
                    dictTarget[name].MarkForDrop(true);
                }
            //else 

        }



        //TODO - Identity changes is not supported for columns
        private void MatchObjects(SqlSmoObject source, SqlSmoObject target)
        {
            foreach (Property p in source.Properties)
            {
                if (p.Writable)
                    if (((p.Value is bool) && ((bool)target.Properties[p.Name].Value != (bool)(p.Value))) ||
                        ((p.Value is int) && ((int)target.Properties[p.Name].Value != (int)p.Value)) ||
                        ((p.Value is string) && (string.CompareOrdinal((string)target.Properties[p.Name].Value, (string)p.Value) != 0)))
                        target.Properties[p.Name].Value = p.Value;
            }
        }


        private bool AreEqual(SqlSmoObject source, SqlSmoObject target)
        {
            foreach (Property p in source.Properties)
            {
                if (p.Writable)
                    if (((p.Value is bool) && ((bool)target.Properties[p.Name].Value != (bool)(p.Value))) ||
                        ((p.Value is int) && ((int)target.Properties[p.Name].Value != (int)p.Value)) ||
                        ((p.Value is string) && (string.CompareOrdinal((string)target.Properties[p.Name].Value, (string)p.Value) != 0)))
                        return false;
            }
            return true;
        }

        private bool CompareDataTypes(DataType a, DataType b)
        {
            return (string.CompareOrdinal(a.Name, b.Name) == 0 &&
                a.MaximumLength == b.MaximumLength &&
                a.NumericPrecision == b.NumericPrecision &&
                a.NumericScale == b.NumericScale &&
                string.CompareOrdinal(a.Schema, b.Schema) == 0 &&
                a.SqlDataType.CompareTo(b.SqlDataType) == 0);
        }
        private void CatchExceptions(Action func)
        {
            try
            {
                func.Invoke();
            }
            catch (InvalidPropertyValueException ex)
            {
                throw new SQLToolbox.Types.SQLTooboxDALException(ex.Message, ex);
            }
            catch (ConnectionFailureException ex)
            {
                throw new SQLToolbox.Types.SQLTooboxDALException("Could not connect to the server", ex);
            }
            catch (Exception ex)
            {
                throw new SQLToolbox.Types.SQLTooboxDALException(ex.Message, ex);
            }
        }

        private void _CreateDropScript(SQLToolbox.Types.SchemaObjectBase obj)
        {
            switch (obj.Type)
            {
                case SchemaObjectType.Table:
                    if (target.Tables[obj.Name] == null)
                        throw new SQLToolbox.Types.SQLTooboxDALException("Object not found");
                    target.Tables[obj.Name].Drop();
                    break;
                case SchemaObjectType.View:
                    if (target.Views[obj.Name] == null)
                        throw new SQLToolbox.Types.SQLTooboxDALException("Object not found");
                    target.Views[obj.Name].Drop();
                    break;
                case SchemaObjectType.Procedure:
                    if (target.StoredProcedures[obj.Name] == null)
                        throw new SQLToolbox.Types.SQLTooboxDALException("Object not found");
                    target.StoredProcedures[obj.Name].Drop();
                    break;
                case SchemaObjectType.UserDefinedType:
                    if (target.UserDefinedTypes[obj.Name] == null)
                        throw new SQLToolbox.Types.SQLTooboxDALException("Object not found");
                    target.UserDefinedTypes[obj.Name].Drop();
                    break;
                case SchemaObjectType.UserDefinedDataType:
                    if (target.UserDefinedDataTypes[obj.Name] == null)
                        throw new SQLToolbox.Types.SQLTooboxDALException("Object not found");
                    target.UserDefinedDataTypes[obj.Name].Drop();
                    break;
                case SchemaObjectType.UserDefinedFunction:
                    if (target.UserDefinedFunctions[obj.Name] == null)
                        throw new SQLToolbox.Types.SQLTooboxDALException("Object not found");
                    target.UserDefinedFunctions[obj.Name].Drop();
                    break;
                case SchemaObjectType.Login:
                case SchemaObjectType.Trigger:
                case SchemaObjectType.Index:
                case SchemaObjectType.ForeignKey:
                case SchemaObjectType.Statistic:
                case SchemaObjectType.NotApplicable:
                default:
                    return ;
            }
        }

        private Database GetDatabase(Server server, string databaseName)
        {
            Database db = server.Databases[databaseName];
            if (db == null)
                throw new SQLToolbox.Types.SQLTooboxDALException("Source database not found");
            return db;
        }

        private Script ExtractScript(Server server)
        {
            Script scr = new Script();
            
            foreach (var str in server.ConnectionContext.CapturedSql.Text)
            {
                // Since I didn't find where to puch ScriptingOptions - I'm removing the USE DATABASE strings
                if (!str.Contains ("USE"))
                    scr.Add(str);
            }
            return scr;
          
        }




    }


    internal static class SMOHelper
    {
        internal static Server ConnectToServer(DBConnectionProperties dcp, bool captureSQL)
        {
            try
            {
                var server = new Server(dcp.ServerName);
                if (!dcp.Credentials.IntegratedSecurity)
                {
                    server.ConnectionContext.LoginSecure = false;
                    server.ConnectionContext.Login = dcp.Credentials.Username;
                    server.ConnectionContext.Password = dcp.Credentials.Password;
                }
                else
                    server.ConnectionContext.LoginSecure = true;

                if (captureSQL)
                    server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

                SetDefaultInitFields(server);
                return server;
            }
            catch (InvalidPropertyValueException ex)
            {
                throw new SQLToolbox.Types.SQLTooboxDALException(ex.Message, ex);
            }
        }
        internal static void SetDefaultInitFields(Server server)
        {
            IDictionary<Type, string[]> prop = CreateDefaultPropertiesForDBRetrieval();
            SetPropertiesForDBRetrieval(server, prop);
        }
        internal static void SetDefaultInitFields(Server server, IDictionary<SchemaObjectType, IEnumerable<string>> requiredProperties)
        {

            IDictionary<Type, string[]> prop = CreateDefaultPropertiesForDBRetrieval();
            foreach (SchemaObjectType type in requiredProperties.Keys)
            {
                var t = GetSMOType(type);
                if (!prop.ContainsKey(t))
                    prop.Add(t, requiredProperties[type].ToArray());
                else
                    prop[t] = prop[t].Concat(requiredProperties[type]).ToArray();
            }
            SetPropertiesForDBRetrieval(server, prop);
        }

        internal static IDictionary<Type, string[]> CreateDefaultPropertiesForDBRetrieval()
        {
            //server.SetDefaultInitFields(typeof(ScriptSchemaObjectBase), new string[] { "Name", "Schema", "IsSystemObject" });
            //server.SetDefaultInitFields(typeof(NamedSmoObject), new string[] { "Name", "Schema", "IsSystemObject" });
            var prop = new Dictionary<Type, string[]>();
            prop.Add(typeof(StoredProcedure), new string[] { "Name", "Schema", "IsSystemObject" });
            prop.Add(typeof(View), new string[] { "Name", "Schema", "IsSystemObject" });
            prop.Add(typeof(Table), new string[] { "Name", "Schema", "IsSystemObject" });
            prop.Add(typeof(UserDefinedDataType), new string[] { "Name", "Schema" });
            prop.Add(typeof(UserDefinedFunction), new string[] { "Name", "Schema", "IsSystemObject" });
            return prop;
        }


        private static void SetPropertiesForDBRetrieval(Server server, IDictionary<Type, string[]> properties)
        {

            foreach (Type key in properties.Keys)
            {
                server.SetDefaultInitFields(key, properties[key]);
            }
        }

        internal static ScriptingOptions CreateScriptingOptions()
        {
            return new ScriptingOptions
            {
                AnsiPadding = true,
                AllowSystemObjects = false,
                AppendToFile = false,
                ContinueScriptingOnError = false,
                ConvertUserDefinedDataTypesToBaseType = false,
                IncludeHeaders = false,
                IncludeIfNotExists = false,
                ExtendedProperties = false,
                //	Indexes = true,
                //	Statistics = true,
                Statistics = false,
                TargetServerVersion = SqlServerVersion.Version90,
                ToFileOnly = false,
                //	Triggers = true,
                WithDependencies = false,
                EnforceScriptingOptions = true
            };
        }

        internal static ScriptingOptions CreateScriptingOptionsWithForeignKeys()
        {
            return new ScriptingOptions
            {
                AnsiPadding = true,
                AllowSystemObjects = false,
                AppendToFile = false,
                ContinueScriptingOnError = false,
                ConvertUserDefinedDataTypesToBaseType = false,
                IncludeHeaders = false,
                IncludeIfNotExists = false,
                ExtendedProperties = false,
                //	Indexes = true,
                //	Statistics = true,
                TargetServerVersion = SqlServerVersion.Version90,
                ToFileOnly = false,
                //	Triggers = true,
                WithDependencies = false,
                EnforceScriptingOptions = true,
                DriForeignKeys = true
            };
        }

        internal static Type GetSMOType(SchemaObjectType type)
        {
            switch (type)
            {
                case SchemaObjectType.Table:
                    return typeof(Microsoft.SqlServer.Management.Smo.Table);
                case SchemaObjectType.View:
                    return typeof(Microsoft.SqlServer.Management.Smo.View);
                case SchemaObjectType.Procedure:
                    return typeof(Microsoft.SqlServer.Management.Smo.StoredProcedure);
                case SchemaObjectType.UserDefinedType:
                    return typeof(Microsoft.SqlServer.Management.Smo.UserDefinedType);
                case SchemaObjectType.UserDefinedDataType:
                    return typeof(Microsoft.SqlServer.Management.Smo.UserDefinedDataType);
                case SchemaObjectType.UserDefinedFunction:
                    return typeof(Microsoft.SqlServer.Management.Smo.UserDefinedFunction);
                case SchemaObjectType.Trigger:
                    return typeof(Microsoft.SqlServer.Management.Smo.Trigger);
                case SchemaObjectType.Index:
                    return typeof(Microsoft.SqlServer.Management.Smo.Index);
                case SchemaObjectType.ForeignKey:
                    return typeof(Microsoft.SqlServer.Management.Smo.ForeignKey);
                case SchemaObjectType.Statistic:
                    return typeof(Microsoft.SqlServer.Management.Smo.Statistic);
                case SchemaObjectType.Login:
                case SchemaObjectType.NotApplicable:
                default:
                    return null;
            }
        }





    }


}
